﻿using System;
using MySql.Data.MySqlClient;

namespace BeerManagementSystem
{
    public class MySQLConnector
    {
        //Objekt einer Verbindung zur MySql-Datenbank
        private MySqlConnection _mysqlConn;
        //Objekt eines Ausführbaren MySql Befehls
        private MySqlCommand _mysqlComm;
        //Objekt eines MySql-Readers
        private MySqlDataReader _mysqlReader;

        public MySQLConnector() 
        {
            connect_to_mysql();
        }
        
        private void connect_to_mysql()
        {
            string connection = "Server=helmold.dyndns.org;Database=beerbase;Uid=web;Password=fischernetz13347;";
            try
            {
#if DEBUG
#else
                connection = "Server=localhost;Database=beerbase;Uid=web;Password=fischernetz13347;";
#endif

                _mysqlConn = new MySqlConnection(connection);
                _mysqlComm = _mysqlConn.CreateCommand();
                _mysqlConn.Open();
                LogWriter.Write("MYSQL Connector: " + _mysqlConn.State, LogWriter.LogLevel.Status, this);
            }
            catch (Exception ex)
            {
                LogWriter.Write("MYSQL Connector: " + ex.StackTrace, LogWriter.LogLevel.Error, this);
                //MessageBox.Show(ex.StackTrace);
            }
        }

        public void UpdateUser(User user)
        {
            if (user.Duration_total != null)
            {
                string commandString = "UPDATE user SET Beer_total='" + Convert.ToInt16(user.Beer_total) + "' WHERE UserID='" + Convert.ToInt16(user.ID) + "'"
                                       + ";UPDATE user SET Beer_month='" + Convert.ToInt16(user.Beer_month) + "' WHERE UserID='" + Convert.ToInt16(user.ID) + "'"
                                       + ";UPDATE user SET Beer_day='" + Convert.ToInt16(user.Beer_day) + "' WHERE UserID='" + Convert.ToInt16(user.ID) + "'"
                                       + ";UPDATE user SET Duration_total='" + ((decimal)(user.Duration_total)).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat)
                                       + "' WHERE UserID='" + Convert.ToInt16(user.ID) + "';";
                Command(commandString);
            }
        }
        public void Command(string command)
        {
            
            
            _mysqlComm.CommandText = command;
            try
            {
                _mysqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogWriter.Write("MYSQL Connector: mysqlCommand: " + command +"\n Error: " + ex.StackTrace, LogWriter.LogLevel.Error, this);
                _mysqlConn.Close();
            }
        }

        public string ReadBeerbox(string command)
        {
            try
            {
                connect_to_mysql();
                
                string message = "";
                _mysqlComm.CommandText = command;
                if (_mysqlReader != null) if (!_mysqlReader.IsClosed) _mysqlReader.Close();

                _mysqlReader = _mysqlComm.ExecuteReader();

                while (_mysqlReader.Read())
                {
                    message = (_mysqlReader["Text"] + Convert.ToString(_mysqlReader["Timestamp"]) + _mysqlReader["Name"]);                    
                }           
                if (!_mysqlReader.IsClosed) _mysqlReader.Close();
                return message;
            }
            catch (Exception ex)
            {
                LogWriter.Write("MYSQL Connector: readBeerbox: " + command + "/n Error:" + ex.StackTrace, LogWriter.LogLevel.Error, this);
                return null;
            }
            
        }

        internal void DeleteLastTransaction(User user)
        {
            if (user.Duration_total != null)
            {
                string commandString = "UPDATE user SET Beer_total='" + user.Beer_total + "' WHERE UserID='" + user.ID + "'"
                                       + ";UPDATE user SET Beer_month='" + user.Beer_month + "' WHERE UserID='" + user.ID + "'"
                                       + ";UPDATE user SET Beer_day='" + user.Beer_day + "' WHERE UserID='" + user.ID + "'"
                                       + ";Update user SET Duration_total='" + ((decimal)(user.Duration_total)).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + "' WHERE UserID='" + user.ID + "';";
                Command(commandString);
            }
        }

        internal void AddUser(User user)
        {
            string commandString = "Insert into user (UserID, LastName, FirstName) "
                + "VALUES ('" + user.ID + "', '" + user.LastName + "', '" + user.FirstName + "');";
            Command(commandString);
        }
    }
}
